SQL Server - Conhecimento Básico 2

Se você for a uma entrevista de emprego conhecendo o básico do SQL Server, que é elaborar queries, você estará competindo para um cargo Junior da empresa.

Uma pergunta típica para um junior seria como eu faço uma pesquisa numa tabela com select Case. E aí é só bater com os 2 jeitos típicos que é possível fazer o case. Se ele só respondeu um vale 50% da resposta.
Deve saber também coisas básicas do tipo 'Porque uso o SQL server e não um Excel para controle de estoque' ou 'Quais são as vantagens do SQL Server sobre um Excel'.

Se você já conhece bem T-SQL você pode deixar de ser Junior. No cargo de Pleno você sabe se virar de alguns percalços do trabalho com servidores SQL Server como Deadlocks, criação de índices, otimização de pesquisas.

Uma pergunta típica para um tecnico Pleno seria qual o critério que você usa para verificar a necessidade, criar e atestar o funcionamento de um índice. Se você não ouvir 50% dados , 50% indices, PK (Primary Keys), Showplan o cara não respondeu sua questão. Deve saber desenvolver procedures, criar objetos dentro do SQL ( como tabelas ) e identificar alguns gargalos. Nem sempre conseguirá resolver todos os problemas.

Outra coisa fundamental é saber explicar o mecanismo do banco de dados. Por exemplo, no Excel o controle da planilha fica na máquina que abriu ela. Isto é muito arriscado, se a rede cai ou o computador crasha de alguma maneira, lá se foi sua planilha. Qualquer um que tenha uma planilha Excel que é acessada em rede por mais de 20 usuários sabe o que estou falando. Perder a planilha passa a ser comum e precisamos gerar um backup.

No SQL Server o mecanismo de banco de dados fica no servidor e com isso garante que não haja falha de rede e esse mecanismo é tolerante a falhas, do tipo, crashou o disco ele poderá, através do log e de backups, recuperar o banco de dados em um tempo hábil relativamente curto.

Se você é um DBA você deverá saber como resolver a grande maioria dos problemas dos servidores SQL como quem está sobrecarregando ou travando o servidor, como otimizar pesquisas, procedures. Como gerenciar o servidor como um todo. Hoje em dia como a maioria dos servidores está na nuvem os DBAs não se debatem mais com a instalação de servidores mas quanto a sua disponibilidade, segurança, performance, confiabilidade, etc.
Até os mais avançados itens de criptografia, segurança e LGPD deve conhecer.

Uma pergunta típíca para um DBA seria qual a influência do Fill Factor na criação de Uma tabela ? Se você não ouvir na resposta 'insert' e 'page split' o cara que respondeu errou.
Você deverá conhecer alguns bancos de dados ( Oracle, SQL Server, MySQL, Postgree), suas ferramentas de gerenciamento e procedimentos de manutenção, especialmente backups ( se não for na nuvem ).
Saberá, por exemplo, qual a diferença do NO_LOCK de um SQL Server e de um Oracle.
Você deverá saber também que um servidor sql por mais que pareça executar

Nota : Um conselho: Nunca pergunte para um DBA como fazer uma querie com um select case. Isso é um insulto, no mínimo. Eu já reprovei empregador por fazer esta pergunta obscena.

Conhecimentos Básicos 2 - De um analista Pleno em SQL Server

Você deverá entender como funciona não só a pesquisa como também como funciona o servidor por dentro. Saber como o servidor processa a pesquisa, o que ele faz e como poderia melhorar as características da pesquisa.

Já ví verdadeiros gênios fazerem algumas pesquisas e ai eu perguntei a ele...onde você espera rodar essa pesquisa? No momento processadores quanticos são raros e os que dispomos na empresa não vão conseguir processar isso nem com todo vento a favor. Pode ser uma querie muito bem feita, evitando deadlocks e usando o que existe de mais sofisticado e correto na linguagem mas esqueceu que é um servidor que vai executar a pesquisa, não um ser de outro planeta.

Conceito 1 - ACID

Se você não conhece este conceito não sabe a diferença entre o bloco de notas e o servidor sql. O ACID garante que você tem um servidor SQL e não um bloco de notas.

ACID é a sigla que significa as principais caracteristicas do servidor SQL : Atomicidade, Consistência, Isolamento e Durabilidade.

Atomicidade Significa que as transações devem ser agrupadas como num lote que deve ser realizado como um todo ( em caso de sucesso ) ou nada do processo deverá ser realizado em caso de erro.
É o caso de uma conta bancária. Ao efetuar o débito de uma conta e o crédito na outra tá tudo certo. Mas se der erro na segunda etapa também não queremos que seja efetuado nem o débido da primeira etapa e o processo será repetido futuramente em momento mais oportuno que garanta sua correta execução.

Consistência Garante que os dados sejam válidos antes e depois da transação.
Isso significa que as tarefas, por exemplo, de integridade referenciais deve feitas não importa se chova ou faça sol. Uma chave pk ou fk (referências de chave estrangeira, por exemplo) devem ser verificados e atualizados para que as informações continuem coesas e obedientes as regras de negócio. Outra coisa é que uma pesquisa feita nos mesmos dados sempre retorne os mesmos resultados. Isto é a base da linguagem SQL também, consistente.

Isolamento É um requisito fundamental para que tarefas de um usuário não atrapalhem ou influenciem as tarefas de outro usuário mesmo quando ocorrem simultaneamente (ao mesmo tempo). No que for possível o servidor irá se virar.
Um transação não pode ver os dados de outra transação que ainda não foi finalizada, mas em vez disso, vê os dados como eram antes do início da transação ou após a transação completa.
Esta é a principal diferença entre o nosso querido Excel e o SQL. Temos centenas de pessoas trabalhando ao mesmo tempo com um banco de dados enquanto que, com o Excel, só um usuário pode alterar a planilha de cada vez.

Durabilidade Significa que os efeitos da transação são armazenados de maneira segura e consistente, e quaisquer informação será recuperável mesmo após falhas do sistema ( se possível e/ou previsto) .
O Mecanismo de banco de dados é feito para tolerar essas falhas gravando informações redundantes para que possibilite sua recuperação no menor período de tempo possível e na mais calamitosa condição de falha.

Lock de transação

O Mecanismo de banco de dados possui recursos para que um usuário não atrapalhe o que o outro esta fazendo. E isto não é um milagre nem algo sobrenatural. São técnicas que possibilitam fazer isso que foram coletadas por anos de experiência em banco de dados.

Você que trabalha com um servidor SQL ficaria preocupado ao fazer uma atualização significativa num servidor de produção se não souber usar os recursos aqui informados.

Existem três tipos de transação possíveis no SQL Server: autocommit, explícito ou implícito.

Autocommit : Este é o comportamento padrão do SQL Server, onde cada instrução Transact-SQL separada que você executa é confirmado automaticamente depois de concluído.
Por exemplo, é possível ter duas instruções INSERT, com a primeira falhando e a segunda acertando; a segunda mudança é mantida porque cada INSERT é automaticamente contidos em sua própria transação.
Embora este modo libere o desenvolvedor de ter que se preocupar com transações explícitas, dependendo desse modo para atividade transacional, pode ser um erro. Por exemplo, se você tiver dois transações, uma que credita uma conta e outra que debita, e a primeira transação falhou, você terá um débito sem o crédito. Isso pode deixar o banco satisfeito, mas não necessariamente o cliente, que teve sua conta debitada.
O autocommit é até um pouco perigoso para mudanças administrativas ad hoc; por exemplo, se você excluir acidentalmente todos linhas de uma tabela, você não tem a opção de reverter a transação depois de perceber o erro, ou aquele famoso update sem where...

As transações implícitas ocorrem quando a sessão do SQL Server abre automaticamente uma nova transação quando uma das seguintes instruções é executada primeiro: ALTER TABLE, FETCH, REVOKE, CREATE, GRANT, SELECT, DELETE, INSERT, TRUNCATE TABLE, DROP, OPEN e UPDATE.
Uma nova transação é criada automaticamente (aberta) assim que qualquer uma das instruções acima mencionadas for executada e permanece aberto até que uma instrução ROLLBACK ou COMMIT seja emitida. O comando de inicialização está incluído no transação aberta.
O modo implícito é ativado executando o seguinte comando em sua sessão de consulta:

SET IMPLICIT_TRANSACTIONS ON;
para desligar:
SET IMPLICIT_TRANSACTIONS OFF;

O modo implícito pode ser muito problemático em um ambiente de produção, porque os designers de aplicativos e os usuários finais podem esquecer de confirmar transações, deixando-as abertas para bloquear outras conexões (mais sobre como bloquear mais adiante no capítulo).

As transações explícitas são aquelas que você mesmo define quando o lock deve ser feito ou liberado.
Este é de longe o modo de operação recomendado ao realizar modificações de dados para seu aplicativo de banco de dados e a maioria dos profissionais usa este recurso.
Isso ocorre porque você controla explicitamente quais modificações pertencem a uma única transação, bem como as ações que são executadas se ocorrer um erro.
As modificações que devem ser agrupadas são feitas usando suas próprias instruções.

Comandos de transação explícitos

Comando	Atividade.
BEGIN TRANSACTION	Define o ponto inicial de uma transação.
ROLLBACK TRANSACTION	Restaura os dados originais modificados por uma transação e traz os dados de volta ao estado em que estavam no início da transação. Os recursos mantidos pela transação são liberados.
COMMIT TRANSACTION	Encerra a transação se nenhum erro for encontrado e torna as alterações permanentes. Os recursos mantidos pela transação são liberados.
BEGIN DISTRIBUTED TRANSACTION	Permite definir o início de uma transação distribuída a ser gerenciada por Coordenador de Transações Distribuídas da Microsoft (MSDTC). MSDTC deve estar em execução local e remotamente.
SAVE TRANSACTION	Emite um savepoint dentro de uma transação, que permite definir um local para o qual uma transação pode retornar se parte da transação for cancelada. Uma transação deve ser revertida ou confirmada imediatamente após a reversão para um ponto de salvamento.
@@TRANCOUNT	Retorna o número de transações ativas para a conexão. BEGIN TRANSACTION incrementa (soma) @@TRANCOUNT de 1. while ROLLBACK TRANSACTION e COMMIT TRANSACTION decrementam(subtraem) 1 do @@TRANCOUNT. ROLLBACK TRANSACTION e COMMIT TRANSACTION criam um ponto de salvamento(save point - uma condição estável e segura).

Caso você queira conhecer como o SQL Server marca um ponto antes e um ponto depois de uma transação veja savepoints. Até a restauração de um backup é feita baseando-se nesses pontos de referencia.

Modos de bloqueio do SQL Server

Como disse acima o mecanismo de banco de dados atua para atender da melhor maneira possível seus usuários e para isso utiliza de técnicas desenvolvidas por dezenas de anos de experiência na área.

Quando falamos de bloqueios a gente pensa numa porta fechada e acabou, sem acesso. Não é bem assim, tem bloqueios inteligentes que deixam passar uma leitura de dados mas não deixam passar uma escrita de dados. É o caso do Shared Lock abaixo.

Note que os Locks não são os mesmos de um banco de dados para outro. Um Oracle trabalha de seu jeito, um SQL Server do seu, um MySQL do seu jeito, um Postgree da sua maneira. Alguns são bem similares e outros bem diferentes.

Nome	Descrição
Shared Lock	Não existe bloqueio para leitura das informações mas existe bloqueio para escrita sobre os dados que estão sendo acessados por outro usuário. Isto é fundamental pois imagine você está obtendo informações do banco de dados e tem 20% das infos antes de uma atualização e 80% das infos depois da atualização. Este tipo de lock permite que os dados sejam lidos mas não atualizado por outros processos enquanto estiver sendo retido/utilizado por outro usuário.
Intent Lock	Como o nome diz, é um lock intencional de um recurso para que possamos fazer uma manutenção em suas informações. Sabemos que, durante essa 'manutenção' ninguém deverá ler as informações porque estarão inconsistentes. Para isto estes bloqueios criam efetivamente uma fila de bloqueio, designando a ordem das conexões e suas direito associado de atualizar ou ler recursos.
Update Lock	Os bloqueios de atualização são criados antes da modificação dos dados. Quando a linha é modificada, este bloqueio é escalado para um bloqueio exclusivo. Se não for modificado, será rebaixado para um bloqueio compartilhado. Este tipo de bloqueio evita deadlocks (discutidos posteriormente neste capítulo) se duas conexões mantiverem um bloqueio compartilhado em um recurso e tentar converter para um bloqueio exclusivo, mas não pode porque eles estão esperando para a outra transação liberar o bloqueio compartilhado.
Exclusive Lock	Este tipo de bloqueio emite um bloqueio no recurso que barra qualquer tipo de acesso (leitura ou gravação). Ele é emitido durante as instruções INSERT, UPDATE e DELETE.
Schema Modification	Esse tipo de bloqueio é emitido quando uma instrução DDL é executada, ou seja, quando mudamos a estrutura de uma tabela ou coisa parecida. Não podemos esquecer que ao alterar uma tabela podemos estar alterando seus itens auxiliares como indices, pks e fks que precisarão ser reconstruidos no final da alteração do recurso e este lock é feito neste sentido.
Schema Statility	Este tipo de bloqueio é emitido quando uma consulta está sendo compilada. Ele evita que as operações DDL sejam realizado na tabela.
Bulk Update	ou Atualização em massa. Esse tipo de bloqueio é emitido durante uma operação de cópia em massa. Isto aumenta o desempenho para a massa operação de cópia, mas a simultaneidade da tabela é reduzida.
Key Range	Os bloqueios de intervalo de chaves protegem um intervalo de linhas (com base na chave de índice) — por exemplo, protegendo linhas em uma instrução UPDATE com um intervalo de datas de 01/01/2022 a 31/12/2022. Protegendo o intervalo de dados impede inserções de linha no intervalo de datas que seriam perdidas pelos dados atuais modificação.

Recursos de bloqueio do SQL Server

Como sabemos os bloqueios muitas vezes são dinâmicos e muitas vezes escaláveis. Começam numa linha, expandem para um range (faixa) e depois a tabela toda em si. Mas exatamente o que pode ser bloqueado pelos locks num servidor SQL ?

Recurso	Descrição
Unidade de alocação	Se você conhece sistema operacional sabe que o acesso a um disco é feito através de clusters ou Unidades de alocação que nada mais são que um conjunto de setores contínuos. O sistema é capaz de ler ou gravar, no mínimo, uma unidade de alocação(cluster). Mudando noss visão para uma tabela sql e como as tabelas de banco de dados são gravadas em disco e gerenciadas tanto pelo sql como pelo sistema operacional, as unidades de alocação são um conjunto de páginas relacionadas agrupadas por tipo de dados, por exemplo, linhas de dados, linhas de índice e grandes linhas de dados do objeto. Normalmente são 8 Mega-Bytes mas isto pode mudar pela configuração de disco ou de servidor.
Row ou linha ou registro	Neste caso apenas a atualização de determinados registros de uma tabela são inibidos até que a atividade atualmente em execução termine.
Tabelas	Quando muitas linhas de uma tabela sofrem lock o lock é automaticamente escalado para a tabela. Neste caso ninguém conseguiria acessar a tabela até que a atividade atualmente em execução termine.
Extent	No SQL Server a unidade de alocação típica tem oito páginas de índice ou dados de 8 KB contíguas.
Banco de dados	Caso muitas tabelas estejam sofrendo lock o sistema escala os locks de tabela para um bloqueio de banco de dados inteiro. É muito raro acontecer e este é um bom motivo para nunca dar um DBCC num banco de dados em produção.
Aplicativo	Ocorre um lock de aplicativo quando este é público e multiusuário mas, no momento, está executando uma tarefa de alta prioridade e importância para o sistema. Sendo assim efetuando um lock no aplicativo eu reservo ele para sua atividade atual e protejo ele de interrupções inoportunas. Normalmente esse aplicativo é um recurso interno do servidor sql que efetua manutenções como reconstrução de um índice ou a atualização de uma estatística em tabelas muito grandes.
Arquivo	Efetua o lock num arquivo de banco de dados. Muito usado antes de executarmos um attach ou detach de um arquivo de backup do sql de um servidor para outro.
HOBT	O lock aqui é feito numa tabela tipo heap (tabela sem índice clusterizado) ou árvore Binária.
Metadados	Metadados do sistema como configurações e controles que mantém o servidor sql no ar.
Key	Bloqueio de índice de chave de linha, ajudando a evitar leituras fantasmas.
Objeto	Um objeto de banco de dados (por exemplo, uma tabela, exibição, procedimento armazenado, função).
Página	Uma página de dados ou índice de 8 KB.
RID	(ROW ID) Identificador de linha, designando uma única linha da tabela.
Table	Um recurso que bloqueia tabela, dados e índices inteiros.

Interações entre os locks e as tarefas

Quando existe um lock o recurso está restrito e um segundo usuário terá que esperar até que a tarefa atualmente em execução termine. Mas e se eu 'afrouxar' esse lock, posso ter ganhos? A resposta é sim e não, depende da tabela. Se a tabela for de atualização muito baixa podemos afrouxar os locks sem muitos problemas mas se for de alto indice de atualização, ai poderemos ter problemas. Se não fizermos o cara esperar, o que ele vai ler ? Sujeira ? Mais ou menos isso.

O padrão ANSI/ISO SQL define quatro tipos de interações entre transações simultâneas.

• Leituras sujas - Dirty reads : Ocorrem enquanto uma transação está atualizando uma linha e uma segunda transação lê a linha antes que a primeira transação seja confirmada.
Se a atualização original for revertida, as alterações não confirmadas serão lidas pela segunda transação, mesmo que sejam nunca comprometido com o banco de dados. Esta é a definição de uma leitura suja.

• Leituras não repetíveis - Nonrepeatable reads: Ocorrem quando uma transação está atualizando dados e uma segunda está lendo os mesmos dados enquanto a atualização está em andamento.
Os dados recuperados antes da update não corresponderá aos dados recuperados após a atualização.

• Leituras fantasmas-Phantom reads: ocorrem quando uma transação emite duas leituras e entre as duas lê, os dados subjacentes são atualizados com os dados sendo inseridos ou excluídos.
Isso faz com que o resultados de cada consulta sejam diferentes.
Linhas retornadas em uma consulta que não aparecem na outra são chamadas de linhas fantasmas.

• Atualizações perdidas - Lost updates : isso ocorre quando duas transações atualizam o valor de uma linha e a transação para última atualização da linha ganha. Assim, a primeira atualização é perdida.

Níveis de Isolamento do SQL Server

Como disse acima podemos ter ganhos significativos reduzindo o lock mas isso também traz alguns riscos. O segredo é saber o recurso certo no momento certo.

Nível de isolamento	Descrição
READ COMMITTED	Este é o comportamento padrão do SQL Server. Quando o READ COMMITTED é usado, as modificações de dados não confirmadas não podem ser lidas. Bloqueios compartilhados são usados ​​durante uma consulta e os dados não podem ser modificados por outros processos enquanto a consulta está recuperando os dados. Inserções e modificações de dados para a mesma tabela são permitidos por outras transações, desde que as linhas envolvidos não são bloqueados pela primeira transação.
READ UNCOMMITTED	Este é o nível de isolamento menos restritivo, não emitindo bloqueios nos dados selecionados pela transação. Isso fornece a maior simultaneidade, mas a menor quantidade de integridade de dados, porque os dados que você lê podem ser alterados enquanto você lê-lo (como mencionado anteriormente, essas leituras são conhecidas como leituras sujas) ou novas podem ser adicionados ou removidos dados que alterariam os resultados da consulta original.Esta opção permite ler dados sem bloquear outros, mas com o perigo de leitura de dados “em fluxo” que podem ser modificados durante a própria leitura (incluindo lendo alterações de dados de uma transação que acaba sendo revertida). Para dados relativamente estáticos e imutáveis, esse nível de isolamento pode potencialmente melhora o desempenho instruindo o SQL Server a não emitir bloqueio dos recursos acessados.
REPEATABLE READ
	Quando habilitada, leituras sujas e não repetíveis não são permitidas. Isso é alcançado colocando bloqueios compartilhados em todos os recursos de leitura. Novas linhas que podem cair na intervalo de dados retornado por sua consulta pode, no entanto, ainda ser inserido por outros transações.
SERIALIZABLE	Quando ativado, esta é a configuração mais restritiva. Os bloqueios de alcance são colocados no dados com base nos critérios de pesquisa usados ​​para produzir o conjunto de resultados. Isso garante que ações como inserção de novas linhas, modificação de valores ou exclusão de linhas existentes que teriam sido retornadas na consulta original e critérios de pesquisa não são permitidos.
SNAPSHOT ( INSTANTÂNEO )	Este nível de isolamento permite que você leia uma versão transacionalmente consistente de os dados como existiam no início de uma transação. As leituras de dados não bloqueiam modificações de dados — no entanto, a sessão SNAPSHOT não detectará alterações sendo feito.

Agora se você entende as explicações desta página e colocou na sua cabeça podemos dizer que você, por mais júnior que seja, não é mais virgem em SQL Server e poderá provar para qualquer um que tem as noções fundamentais, não todas mas as mais importantes. Boa sorte.